How does a select statement works in oracle

SELECT statement follows following steps
1. parse : acquires library cache lock(aka DDL lock) and downgrade it to null mode(aka breakable parse lock)
2. execute : acquires library cache pin and release it
3. fetch: breakable parse lock
4. close: release breakable parse lock

Library cache locks can only be broken, however, when the library cache object is not also pinned. A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed, or executed. (no fetch)

Fetch phase does't REALLY NEED a library cache pin, A simple select query 'select * from a big_table' (1million-rows in big_table),does not read all rows at once, every fetch call return a slice rows ,use it,then next fetch call,next slice. etc.. you might do a fetch, walk away for a week, and then come back. We can't really hold the table "locked" for an unknown duration. That's why we can allow things to happen to the table while you are fetching.

 

When we run a trace and TKPROF on a query (a select statement), we see timing information for three phases:

1.)parse - pretty well defined, that is prepareStatement - we do a soft or hard parse, compile the statement, figure out how to execute it.

2) execute - we OPEN the statement. For an update, for a delete, for an insert - that would be it, when you OPEN the statement, we execute it. All of the work happens here.

for select it is more complex. Most selects will do ZERO work during the execute. All we are doing is opening the cursor - the cursor is a pointer to the space in the shared pool where the plan is, your bind variable values, the SCN that represents the "as of" time for your query - in short the cursor at this point is your context, your virtual machine state, think of the SQL plan as if it were bytecode (it is) executed as a program (it is) in a virtual machine (it is). The cursor is your instruction pointer (where are you in the execution of this statement), your state (like registers), etc. Normally, a select does nothing here - it just "gets ready to rock and roll, the program is ready to go, but not yet really started".

However, there are exceptions to everything - turn on trace and do a select * from scott.emp FOR UPDATE. That is a select, but it is also an update. You would see work done during the execute as well as the fetch phase. The work done during the execute was that of going out and touching every row and locking it. The work done during the fetch phase was that of going out and retrieving the data back to the client.

3) fetch - this is where we see almost all of the work for SELECTS (and nothing really for the other DMLS as you do not fetch from an update).

There are two ways a SELECT might be processed. What I call a "quick return query" and a "slow return query"

select * from one_billion_row_table;

would not copy the data anywhere, would not need to access the last row before returning the first row. We would just read the data as you fetch it from the blocks it resides on.

However, a query of the form:

select * from one_billion_row_table order by unindexed_column;

that we would probably have to read the last row before returning the first row (since the last row read could well be the first row returned!) and we'd need to copy that somewhere (temp, sort area space) first.

 

SELECT /*+ FIRST_ROWS(1) */ * from big_table order by owner

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----- ----- ---------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ----- ----- ---------- -----
total 3 0.00 0.00 0 4 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BIG_TABLE
1 INDEX FULL SCAN BIG_TABLE_IDX